

/****** Object:  View [dbo].[v_td_poscounter]    Script Date: 10/24/2017 15:43:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


Alter            view [dbo].[v_td_poscounter]
as 

select m.*, d.startAmount, d.EndAmount, d.isamount as isamount, d.rate as rate
from (select convert(varchar(10), m.btime,121) as PosBillDay , substring (convert(varchar(19), m.btime,121), 12, len(convert(varchar(19), m.btime,121)))  as Posbtime, m.*
from tb_possale m  
where 1 = 1  and m.bid>=0 and m.bstate=1 and m.counterid is not null )m  
left join (
select convert(varchar(10), d.startDate,121) as StartRateDay , substring (convert(varchar(19), d.startTime,121), 12, len(convert(varchar(19), d.startTime,121)))  as StartRateTime,
convert(varchar(10), d.EndDate,121) as EndRateDay,   substring (convert(varchar(19), d.EndTime,121), 12, len(convert(varchar(19), d.EndTime,121)))  as EndRateTime,
m.isamount, d.*
from td_poscounter m
left join td_poscounterrate d on m.id=d.counterid and m.isamount= 1
)  d on m.counterid=d.id 
where (m.PosBillDay between StartRateDay and EndRateDay)
union all
select m.*, d.startAmount, d.EndAmount, d.isamount as isamount, d.rate  as rate
from (select convert(varchar(10), m.btime,121) as PosBillDay , substring (convert(varchar(19), m.btime,121), 12, len(convert(varchar(19), m.btime,121)))  as Posbtime, m.*
from tb_possale m  
where 1 = 1  and m.bid>=0 and m.bstate=1 and m.counterid is not null )m  
left join (
select convert(varchar(10), d.startDate,121) as StartRateDay , substring (convert(varchar(19), d.startTime,121), 12, len(convert(varchar(19), d.startTime,121)))  as StartRateTime,
convert(varchar(10), d.EndDate,121) as EndRateDay,   substring (convert(varchar(19), d.EndTime,121), 12, len(convert(varchar(19), d.EndTime,121)))  as EndRateTime,
m.isamount, d.*
from td_poscounter m
left join td_poscounterrate d on m.id=d.counterid and (m.isamount= 0 or m.isamount is NULL)
)  d on m.counterid=d.id 
where (m.PosBillDay between StartRateDay and EndRateDay) and (m.Posbtime between StartRateTime and EndRateTime) 















GO


